﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System;
using System.Data;
using System.Data.SqlClient;

namespace frmCheckStock
{
    class EmployeeRequisitionBroker
    {
        // Class level declare data objects 
        SqlConnection cn;
        SqlCommand cmInsert;
        SqlCommand cmUpdate;
        SqlCommand cmDelete;
        SqlCommand cmSelbyPK;
        SqlCommand cmSelAll;
        

        public EmployeeRequisitionBroker()
        {
            // init all the class variables
            string conS = "data source=(local);initial catalog=LogicUniversityT2;integrated security=SSPI";
            cn = new SqlConnection(conS);
            cmInsert = new SqlCommand();
            cmDelete = new SqlCommand();           
            cmSelbyPK = new SqlCommand();

            cmInsert.CommandText = "Insert into EmployeeRequisition (EmployeeRequisitionID, EmployeeID, DepartmentID, EmployeeRequisitionDate, Status, TotalAmount, Remarks) " +
                " values (@EmployeeRequisitionID, @EmployeeID, @DepartmentID, @EmployeeRequisitionDate, @Status, @TotalAmount, @Remarks )";
            cmInsert.Connection = cn;

            cmDelete.CommandText = "Delete from Movies where EmployeeRequisitionID = @EmployeeRequisitionID";
            cmDelete.Connection = cn;

            cmSelbyPK.CommandText = "Select * from movies where EmployeeRequisitionID=@EmployeeRequisitionID";
            cmSelbyPK.Connection = cn;

        }

        public void Insert(EmployeeRequisition employeeRequisitoin)
        {
            //set command parameter to entity values eg: pVC = movie.VideoCode
            SqlParameter pVideoCode = new SqlParameter("@VideoCode", SqlDbType.Int);
            SqlParameter pMovieTitle = new SqlParameter("@MovieTitle", SqlDbType.NVarChar, 100);
            SqlParameter pMovieType = new SqlParameter("@MovieType", SqlDbType.NVarChar, 20);
            SqlParameter pRating = new SqlParameter("@Rating", SqlDbType.NVarChar, 6);
            SqlParameter pRentalPrice = new SqlParameter("@RentalPrice", SqlDbType.Decimal);
            SqlParameter pProducer = new SqlParameter("@Producer", SqlDbType.NVarChar, 100);
            SqlParameter pDirector = new SqlParameter("@Director", SqlDbType.NVarChar, 100);
            SqlParameter pMedia = new SqlParameter("@Media", SqlDbType.NVarChar, 100);
            SqlParameter pTotalStock = new SqlParameter("@TotalStock", SqlDbType.Int);
            SqlParameter pNumberRented = new SqlParameter("@NumberRented", SqlDbType.Int);
            cmInsert.Parameters.AddRange(new SqlParameter[] { pVideoCode, pMovieTitle, pMovieType, pRating, pRentalPrice, pProducer, pDirector, pMedia, pTotalStock, pNumberRented });

            pVideoCode.Value = movie.VideoCode;
            pMovieTitle.Value = movie.MovieTitle;
            pMovieType.Value = movie.MovieType;
            pRating.Value = movie.Rating;
            pRentalPrice.Value = movie.RentalPrice;
            pProducer.Value = movie.Producer;
            pDirector.Value = movie.Director;
            pMedia.Value = movie.VideoCode;
            pTotalStock.Value = movie.TotalStock;
            pNumberRented.Value = movie.NumberRented;

            // fire the insert command
            cn.Open();
            cmInsert.ExecuteNonQuery();
            cn.Close();

        }

        public Movie GetMovie(int videoCode)
        {
            // fire the select command
            SqlParameter pVideoCode = new SqlParameter("@VideoCode", SqlDbType.Int);
            cmSelbyPK.Parameters.Add(pVideoCode);
            pVideoCode.Value = videoCode;

            Movie movie = new Movie();
            // fire the delete command
            cn.Open();
            SqlDataReader rd = cmSelbyPK.ExecuteReader();
            if (rd.Read())
            {
                movie.VideoCode = Convert.ToInt16(rd["VideoCode"].ToString());
                movie.MovieTitle = rd["MovieTitle"].ToString();
                movie.MovieType = rd["MovieType"].ToString();
                movie.NumberRented = Convert.ToInt32(rd["NumberRented"].ToString());
                movie.Producer = rd["Producer"].ToString();
                movie.Rating = rd["Rating"].ToString();
                movie.RentalPrice = Convert.ToDouble(rd["RentalPrice"].ToString());
                movie.TotalStock = Convert.ToInt32(rd["TotalStock"].ToString());
                movie.Media = rd["Media"].ToString();
                movie.Director = rd["Director"].ToString();
                cn.Close();
            }
            else
            {
                cn.Close();
                throw new Exception("Video not found");
            }
            // set values for movie eg: movie.VideoCode = rd["VideoCode"].ToString

            return movie;
        }

        public void Update(Movie movie)
        {
            //set command parameter to entity values eg: pVC = movie.VideoCode
            SqlParameter pVideoCode = new SqlParameter("@VideoCode", SqlDbType.Int);
            SqlParameter pMovieTitle = new SqlParameter("@MovieTitle", SqlDbType.NVarChar, 100);
            SqlParameter pMovieType = new SqlParameter("@MovieType", SqlDbType.NVarChar, 20);
            SqlParameter pRating = new SqlParameter("@Rating", SqlDbType.NVarChar, 6);
            SqlParameter pRentalPrice = new SqlParameter("@RentalPrice", SqlDbType.Decimal);
            SqlParameter pProducer = new SqlParameter("@Producer", SqlDbType.NVarChar, 100);
            SqlParameter pDirector = new SqlParameter("@Director", SqlDbType.NVarChar, 100);
            SqlParameter pMedia = new SqlParameter("@Media", SqlDbType.NVarChar, 100);
            SqlParameter pTotalStock = new SqlParameter("@TotalStock", SqlDbType.Int);
            SqlParameter pNumberRented = new SqlParameter("@NumberRented", SqlDbType.Int);
            cmUpdate.Parameters.AddRange(new SqlParameter[] { pVideoCode, pMovieTitle, pMovieType, pRating, pRentalPrice, pProducer, pDirector, pMedia, pTotalStock, pNumberRented });

            pVideoCode.Value = movie.VideoCode;
            pMovieTitle.Value = movie.MovieTitle;
            pMovieType.Value = movie.MovieType;
            pRating.Value = movie.Rating;
            pRentalPrice.Value = movie.RentalPrice;
            pProducer.Value = movie.Producer;
            pDirector.Value = movie.Director;
            pMedia.Value = movie.VideoCode;
            pTotalStock.Value = movie.TotalStock;
            pNumberRented.Value = movie.NumberRented;

            // fire the update command
            cn.Open();
            cmUpdate.ExecuteNonQuery();
            cn.Close();
        }

        public void Delete(Movie movie)
        {

            //set command parameter to entity values eg: pVC = movie.VideoCode
            SqlParameter pVideoCode = new SqlParameter("@VideoCode", SqlDbType.Int);
            cmDelete.Parameters.Add(pVideoCode);
            pVideoCode.Value = movie.VideoCode;

            // fire the delete command
            cn.Open();
            cmDelete.ExecuteNonQuery();
            cn.Close();
        }

        public List<Movie> GetAll()
        {
            cmSelAll = new SqlCommand("Select * from Movies", cn);
            cn.Open();
            SqlDataReader rd = cmSelAll.ExecuteReader();
            List<Movie> movieList = new List<Movie>();
            while (rd.Read())
            {
                Movie movie = new Movie();
                movie.VideoCode = Convert.ToInt16(rd["VideoCode"].ToString());
                object o = rd["VideoCode"];
                movie.MovieTitle = rd["MovieTitle"].ToString();
                movie.MovieType = rd["MovieType"].ToString();
                movie.NumberRented = Convert.ToInt32(rd["NumberRented"].ToString());
                movie.Producer = rd["Producer"].ToString();
                movie.Rating = rd["Rating"].ToString();
                movie.RentalPrice = Convert.ToDouble(rd["RentalPrice"].ToString());
                movie.TotalStock = Convert.ToInt32(rd["TotalStock"].ToString());
                movie.Media = rd["Media"].ToString();
                movie.Director = rd["Director"].ToString();
                movieList.Add(movie);
            }

            return movieList;
        }

    }
}
